package com.tuo51.evaluate.repositories.evaluatev2;

import com.tuo51.evaluate.domain.evaluatingV2.EvaluateV2Item;
import org.springframework.cache.annotation.CacheConfig;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@CacheConfig(cacheNames = "EvaluateV2ItemRepository")
public interface EvaluateV2ItemRepository extends JpaRepository<EvaluateV2Item, String> {

    @Cacheable(keyGenerator = "keyGenerator")
    @Query(value = "SELECT t.*  " +
            "from " +
            "   tb_evaluate_item t  left join tb_evaluate_question q on q.itemId = t.id " +
            "where " +
            "   q.id = :questionId " +
            "limit 1", nativeQuery = true)
    EvaluateV2Item getOneByQuestionId(@Param("questionId") String questionId);

    @Query(value = "SELECT t.* " +
            "FROM " +
            "    (SELECT " +
            "        ei.* " +
            "    FROM " +
            "        tb_evaluate_record_answer era " +
            "    LEFT JOIN tb_evaluate_question eq ON eq.id = era.questionId " +
            "    LEFT JOIN tb_evaluate_item ei ON ei.id = eq.itemId " +
            "    WHERE " +
            "        era.recordId = :recordId and era.checked = false " +
            "    GROUP BY dimensionId " +
            "    ORDER BY month) t " +
            "    left join tb_evaluate_dimension ed on ed.id = t.dimensionId " +
            "WHERE " +
            "    `bringUp` IS NOT NULL " +
            "        OR `developmentStatus` IS NOT NULL " +
            "        OR `parentManual` IS NOT NULL " +
            "        OR `guide` IS NOT NULL " +
            "ORDER BY ed.code", nativeQuery = true)
    List<EvaluateV2Item> findAllFailedQuestionItems(@Param("recordId") String recordId);



    //根據月份和維度查找對應的評測item
    List<EvaluateV2Item> findAllByDimensionIdAndMonthAndDeleted(String dimensionId, Integer month, Boolean deleted);

    //
    @Cacheable(keyGenerator = "keyGenerator")
    List<EvaluateV2Item> findTop2ByDimensionIdAndMonthLessThanAndDeletedOrderByMonthDesc(String dimensionId, Integer month, Boolean deleted);

    List<EvaluateV2Item> findTop2ByDimensionIdAndDeletedOrderByMonth(String dimensionId, Boolean deleted);

    @Cacheable(keyGenerator = "keyGenerator")
    @Query(value = "SELECT i.* FROM `tb_evaluate_item` i LEFT JOIN `tb_evaluate_dimension` d on d.`id` = i.`dimensionId`  ORDER BY d.`code` asc, i.month asc", nativeQuery = true)
    List<EvaluateV2Item> findAllByOrderByDimensionCodeAscMonthAsc();

    @Cacheable(keyGenerator = "keyGenerator")
    List<EvaluateV2Item> findAllByDimensionIdOrderByMonth(String dimensionId);

    //找到某个条目之后的一个条目
    @Cacheable(keyGenerator = "keyGenerator")
    EvaluateV2Item findFirstByDimensionIdAndMonthGreaterThanAndDeletedOrderByMonth(String dimensionId, Integer month, Boolean deleted);

    //找到某个条目之后的一个条目
    @Cacheable(keyGenerator = "keyGenerator")
    List<EvaluateV2Item> findTop2ByDimensionIdAndMonthGreaterThanAndDeletedIsFalseOrderByMonth(String dimensionId, Integer month);

    //找不到的时候，找个缺省条目
    @Cacheable(keyGenerator = "keyGenerator")
    EvaluateV2Item findFirstByDimensionIdAndDeletedOrderByMonth(String dimensionId, Boolean deleted);

    @Cacheable(keyGenerator = "keyGenerator")
    EvaluateV2Item findTopByDimensionIdAndMonth(String dimensionId, int month);

}
